package com.yc.ssq.dao;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;

import com.yc.common.util.DBHelper;

public class SsqDao {
    public void save(File file) {
        // 1. 方案一,组装一个超大的sql语句
        // 2. 方案二,循环insert 不能用DBHelper.update()
        Connection connection = null;
        try (FileReader fr = new FileReader(file);
             BufferedReader br = new BufferedReader(fr)) {
            connection = DBHelper.getConnection();
            String sql = "insert into lottery values (default ,?,?,?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement ps = connection.prepareStatement(sql);
            String line;
            while ((line = br.readLine()) != null) {
                System.out.println(line);
                String[] values = line.split("\\s+");
                System.out.println(Arrays.toString(values));
                for (int i = 0; i < values.length; i++) {
                    ps.setString(i + 1, values[i]);
                }
                ps.executeUpdate();
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("数据文件保存失败", e);
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException("数据库连接关闭失败", e);
            }
        }
    }

    /**
     * 分页查询 + 组合条件查询, 返回 2 个数据: 当前页list, 总行数
     */
    public Map<String, Object> selectPage(
            String begin, String end, int page, int size) {
        String sql = "select * from lottery where 1=1";
        List<Object> params = new ArrayList<>();
        if (begin != null && begin.trim().isEmpty() == false) {
            sql += " and opendate >= ?";
            params.add(begin);
        }
        if (end != null && end.trim().isEmpty() == false) {
            sql += " and opendate <= ?";
            params.add(end);
        }
        // 总行数
        long total = DBHelper.count(sql, params.toArray());
        // 当前页数据
        sql += " limit ? , ?";
        params.add(page * size);  // 起始行
        params.add(size);         // 每页行数
        List<Map<String, Object>> list = DBHelper.selectListMap(sql, params.toArray());
        // 将数据封装到 map 集合中
        Map<String, Object> res = new HashMap<>();
        res.put("total", total);
        res.put("list", list);
        return res;
    }

    public List<Map<String, Object>> selectQxt(String year) {
        String sql = "SELECT\n" +
                "	SUBSTR(a.opendate, 6, 2) mon,\n" +
                "	sum(REPLACE(a.sale, ',', '') + 0) / 10000000 money\n" +
                "FROM\n" +
                "	lottery a\n" +
                "where a.opendate like ?\n" +
                "group by SUBSTR(a.opendate, 6, 2)";
        return DBHelper.selectListMap(sql, year + '%');
    }

    public List<Map<String, Object>> selectYears() {
        String sql = "SELECT distinct \n" +
                "	SUBSTR(a.opendate, 1, 4) year\n" +
                "FROM\n" +
                "	lottery a order by id desc";
        return DBHelper.selectListMap(sql);
    }

    // 柱状图
    public List<Map<String, Object>> selectZzt(String[] years, String month) {
        List<Object> params = new ArrayList<>();
        String yearsStr = "";
        // 构建 ?,?,? 年份参数
        for (int i = 0; i < years.length; i++) {
            if (i > 0) {
                yearsStr += ",";
            }
            yearsStr += "?";
            params.add(years[i]);
        }
        // 添加月份参数
        params.add(month);
        String sql = "SELECT\n" +
                "	substr(opendate, 1, 4) YEAR,\n" +
                "	substr(opendate, 6, 2) MONTH,\n" +
                "	sum(REPLACE(sale, ',', '') + 0) / 10000000 sale\n" +
                "FROM\n" +
                "	lottery\n" +
                "WHERE\n" +
                "	substr(opendate, 1, 4) IN (###)\n" +
                "AND substr(opendate, 6, 2) = ?\n" +
                "GROUP BY\n" +
                "	substr(opendate, 1, 4),\n" +
                "	substr(opendate, 6, 2)";
        sql = sql.replace("###",yearsStr);
        return DBHelper.selectListMap(sql, params.toArray());
    }

}
